Skip Headers

Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Example: Creating a Level

The following statements create four levels for the `PRODUCT_DIM` dimension and assign them to the `PRODUCT_DIM_ROLLUP` hierarchy.

    execute cwm2_olap_level.create_level
         ('JSMITH', 'PRODUCT_DIM', 'TOTALPROD_LVL',
          'Total Product', 'All Products', 'Total',
          'Equipment and Parts of standard product hierarchy');
    execute cwm2_olap_level.create_level
         ('JSMITH', 'PRODUCT_DIM', 'PROD_CATEGORY_LVL',
          'Product Category', 'Product Categories', 'Category',
          'Categories of standard product hierarchy');
    execute cwm2_olap_level.create_level
         ('JSMITH', 'PRODUCT_DIM', 'PROD_SUBCATEGORY_LVL',
          'Product Sub-Category', 'Product Sub-Categories', 'Sub-Category',
          'Sub-Categories of standard product hierarchy');
    execute cwm2_olap_level.create_level
         ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_LVL',
          'Product', 'Products', 'Product',
          'Individual products of standard product hierarchy');

    execute cwm2_olap_level.add_level_to_hierarchy
         ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_DIM_ROLLUP',
          'PRODUCT_LVL', 'PROD_SUBCATEGORY_LVL');
    execute cwm2_olap_level.add_level_to_hierarchy
         ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_DIM_ROLLUP',
          'PROD_SUBCATEGORY_LVL', 'PROD_CATEGORY_LVL');
    execute cwm2_olap_level.add_level_to_hierarchy
         ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_DIM_ROLLUP',
          'PROD_CATEGORY_LVL', 'TOTALPROD_LVL');
    execute cwm2_olap_level.add_level_to_hierarchy
         ('JSMITH', 'PRODUCT_DIM', 'PRODUCT_DIM_ROLLUP', 'TOTALPROD_LVL');

# Summary of CWM2_OLAP_LEVEL Subprograms

Table 15-1 CWM2_OLAP_LEVEL Subprograms

Subprogram | Description
--- | ---
ADD_LEVEL_TO_HIERARCHY Procedure | Adds a level to a hierarchy.
CREATE_LEVEL Procedure | Creates a level.
DROP_LEVEL Procedure | Drops a level.
LOCK_LEVEL Procedure | Locks the level metadata for update.
REMOVE_LEVEL_FROM_HIERARCHY Procedure | Removes a level from a hierarchy.
SET_DESCRIPTION Procedure | Sets the description for a level.
SET_DISPLAY_NAME Procedure | Sets the display name for a level.
SET_LEVEL_NAME Procedure | Sets the name of a level.
SET_PLURAL_NAME Procedure | Sets the plural name for a level.
SET_SHORT_DESCRIPTION Procedure | Sets the short description for a level.

## ADD_LEVEL_TO_HIERARCHY Procedure

This procedure adds a level to a hierarchy. A hierarchy can have a maximum of 31 levels.

Syntax

    ADD_LEVEL_TO_HIERARCHY (
              dimension_owner     IN   VARCHAR2,
              dimension_name      IN   VARCHAR2,
              hierarchy_name      IN   VARCHAR2,
              level_name          IN   VARCHAR2,
              parent_level_name   IN   VARCHAR2  DEFAULT NULL);

Parameters

Table 15-2 ADD_LEVEL_TO_HIERARCHY Procedure Parameters

Parameter | Description
--- | ---
`dimension_owner` | Owner of the dimension.
`dimension_name` | Name of the dimension.
`hierarchy_name` | Name of the hierarchy.
`level_name` | Name of the level to add to the hierarchy.
`parent_level_name` | Name of the level's parent in the hierarchy. If you do not specify a parent, then the added level is the root of the hierarchy.

## CREATE_LEVEL Procedure

This procedure creates a new level in the OLAP Catalog.

You must specify descriptions and display properties as part of level creation. Once the level has been created, you can override these properties by calling other procedures in the `CWM2_OLAP_LEVEL` package.

Syntax

    CREATE_LEVEL (
              dimension_owner       IN   VARCHAR2,
              dimension_name        IN   VARCHAR2,
              level_name            IN   VARCHAR2,
              display_name          IN   VARCHAR2,
              plural_name           IN   VARCHAR2,
              short_description     IN   VARCHAR2,
              description           IN   VARCHAR2);

Parameters

Table 15-3 CREATE_LEVEL Procedure Parameters

Parameter | Description
--- | ---
`dimension_owner` | Owner of the dimension.
`dimension_name` | Name of the dimension.
`level_name` | Name of the level.
`display_name` | Display name for the level.
`plural_name` | Plural name for the level.
`short_description` | Short description of the level.
`description` | Description of the level.

## DROP_LEVEL Procedure

This procedure drops a level from the OLAP Catalog. All related level attributes are also dropped.

Syntax

    DROP_LEVEL (
             dimension_owner     IN   VARCHAR2,
             dimension_name      IN   VARCHAR2,
             level_name          IN   VARCHAR2);

Parameters

Table 15-4 DROP_LEVEL Procedure Parameters

Parameter | Description
--- | ---
`dimension_owner` | Owner of the dimension.
`dimension_name` | Name of the dimension.
`level_name` | Name of the level.

## LOCK_LEVEL Procedure

This procedure locks the level metadata for update by acquiring a database lock on the row that identifies the level in the `CWM2` model table.

Syntax

    LOCK_LEVEL (
               dimension_owner     IN   VARCHAR2,
               dimension_name      IN   VARCHAR2,
               level_name          IN   VARCHAR2,
               wait_for_lock       IN   BOOLEAN DEFAULT FALSE);

Parameters

Table 15-5 LOCK_LEVEL Procedure Parameters

Parameter | Description
--- | ---
`dimension_owner` | Owner of the dimension.
`dimension_name` | Name of the dimension.
`level_name` | Name of the level.
`wait_for_lock` | (Optional) Whether or not to wait for the level to be available when it is already locked by another user. If you do not specify a value for this parameter, the procedure does not wait to acquire the lock.

## REMOVE_LEVEL_FROM_HIERARCHY Procedure

This procedure removes a level from a hierarchy.

Syntax

    REMOVE_LEVEL_FROM_HIERARCHY (
              dimension_owner     IN   VARCHAR2,
              dimension_name      IN   VARCHAR2,
              hierarchy_name      IN   VARCHAR2,
              level_name          IN   VARCHAR2);

Parameters

Table 15-6 REMOVE_LEVEL_FROM_HIERARCHY Procedure Parameters

Parameter | Description
--- | ---
`dimension_owner` | Owner of the dimension.
`dimension_name` | Name of the dimension.
`hierarchy_name` | Name of the hierarchy.
`level_name` | Name of the level to remove from the hierarchy.

## SET_DESCRIPTION Procedure

This procedure sets the description for a level.

Syntax

    SET_DESCRIPTION (
              dimension_owner     IN   VARCHAR2,
              dimension_name      IN   VARCHAR2,
              level_name          IN   VARCHAR2,
              description         IN   VARCHAR2);

Parameters

Table 15-7 SET_DESCRIPTION Procedure Parameters

Parameter | Description
--- | ---
`dimension_owner` | Owner of the dimension.
`dimension_name` | Name of the dimension.
`level_name` | Name of the level.
`description` | Description of the level.

## SET_DISPLAY_NAME Procedure

This procedure sets the display name for a level.

Syntax

    SET_DISPLAY_NAME (
              dimension_owner     IN   VARCHAR2,
              dimension_name      IN   VARCHAR2,
              level_name          IN   VARCHAR2,
              display_name        IN   VARCHAR2);

Parameters

Table 15-8 SET_DISPLAY_NAME Procedure Parameters

Parameter | Description
--- | ---
`dimension_owner` | Owner of the dimension.
`dimension_name` | Name of the dimension.
`level_name` | Name of the level.
`display_name` | Display name for the level.

## SET_LEVEL_NAME Procedure

This procedure sets the name for a level.

Syntax

    SET_LEVEL_NAME (
              dimension_owner   IN   VARCHAR2,
              dimension_name    IN   VARCHAR2,
              level_name        IN   VARCHAR2,
              set_level_name    IN   VARCHAR2);

Parameters

Table 15-9 SET_LEVEL_NAME Procedure Parameters

Parameter | Description
--- | ---
`dimension_owner` | Owner of the dimension.
`dimension_name` | Name of the dimension.
`level_name` | Original name for the level.
`set_level_name` | New name for the level.

## SET_PLURAL_NAME Procedure

This procedure sets the plural name of a level.

Syntax

    SET_PLURAL_NAME  (
              dimension_owner     IN   VARCHAR2,
              dimension_name      IN   VARCHAR2,
              level_name          IN   VARCHAR2,
              plural_name         IN   VARCHAR2);

Parameters

Table 15-10 SET_PLURAL_NAME Procedure Parameters

Parameter | Description
--- | ---
`dimension_owner` | Owner of the dimension.
`dimension_name` | Name of the dimension.
`level_name` | Name of the level.
`plural_name` | Plural name for the level.

## SET_SHORT_DESCRIPTION Procedure

This procedure sets the short description for a level.

Syntax

    SET_SHORT_DESCRIPTION (
              dimension_owner       IN   VARCHAR2,
              dimension_name        IN   VARCHAR2,
              level_name            IN   VARCHAR2,
              short_description     IN   VARCHAR2);

Parameters

Table 15-11 SET_SHORT_DESCRIPTION Procedure Parameters

Parameter | Description
--- | ---
`dimension_owner` | Owner of the dimension.
`dimension_name` | Name of the dimension.
`level_name` | Name of the level.
`short_description` | Short description of the level.